﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetShowClass]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetShowClass];
GO
CREATE PROCEDURE [dbo].[sproc_GetShowClass] 
    @UserName nvarchar(255),
    @RIGHTCode int = 1,
    @IncludeFunctionNode int = 1 
/*

============================================================
功能:    得到@UserName所有显示的组
参数:
    @UserName nvarchar(255)        :    用户名
    @RIGHTCode int =1        :    浏览权代号
    @IncludeFunctionNode        :    是否包含功能接点

备注:    可以优化
============================================================

*/
AS
BEGIN
SET NOCOUNT ON

DECLARE @Staff_id int
DECLARE @Position_id int

SET @staff_id=0

--取得用户的ID和职位ID
SELECT
    @staff_id = a.staff_id,
    @Position_id = b.Position_id
FROM
    dbo.uds_staff a,
    dbo.uds_staff_in_position b
WHERE
    a.Staff_ID = b.Staff_ID
    and a.staff_name = @username;
IF ( @@rowcount <> 1 )
begin
    raiserror(N'The specified staff "%s" cannot be found.', 16, 1, @username);
    return -1;
end;

DECLARE @ids nvarchar(2000)
SET @ids = ''

DECLARE @tmpPosition TABLE (id int);
INSERT INTO @tmpPosition EXEC sproc_GetAllChildPositionID @Position_id

--得到间接的浏览权，即职位对其下属的项目监督权
--得到该接点的所有子接点
declare @tmpStaff table (staff_id int)

--加入本人
INSERT INTO @tmpStaff (staff_id) values(@staff_id);

--加入所有下级成员
INSERT INTO @tmpStaff ( staff_id )
    SELECT
        a.staff_id
    FROM
        uds_staff a,
        uds_staff_in_position b,
        @tmpPosition c
    WHERE
        a.staff_id = b.staff_id
        and b.position_id = c.id


--创建临时类表
DECLARE @tmpClass TABLE (obj_id int)

--把所有可显示的类 ID 收集起来
--act_id=1为显示权
IF @staff_id>0
BEGIN
    --从职位中，能查看的接点
    INSERT INTO @tmpClass ( obj_id )
        SELECT
            team_id
        FROM
            UDS_Assign_Rule a
        WHERE
            based_on = 1
            and a.act_id = @RIGHTCode
            and a.Position_id in
            (
                SELECT Position_id FROM uds_staff_in_position WHERE staff_id in ( SELECT staff_id FROM @tmpStaff )
            )

    --从项目中，能查看的接点
    INSERT INTO @tmpClass ( obj_id )
        SELECT
            team_id 
        FROM
            UDS_Assign_Rule a
        WHERE
            based_on = 2 
            and a.act_id = @RIGHTCode
            and a.team_id in
            (
                SELECT team_id FROM uds_staff_in_team WHERE staff_id in (SELECT staff_id FROM @tmpStaff)
--是否只有成员拥有项目的权利
--                               and member_type = 1
            )


    --项目角色，能查看的接点
    INSERT INTO @tmpClass ( obj_id )
        SELECT
            A.team_id
        FROM
            uds_staff_in_team a,
            uds_activity b
        WHERE
            a.member_type = b.act_id
            and b.proc_id = @RIGHTCode
            and a.staff_id in (SELECT staff_id FROM @tmpStaff)

    --从角色中，能查看的接点
    INSERT INTO @tmpClass ( obj_id )
        SELECT
            team_id 
        FROM 
            UDS_Assign_Rule a
        WHERE
            a.role_id in (SELECT role_id FROM uds_staff_in_role WHERE staff_id = @staff_id)
            and based_on = 3
            and a.act_id = @RIGHTCode

    --从个人中，能查看的接点
    INSERT INTO @tmpClass ( obj_id )
        SELECT
            team_id 
        FROM 
            UDS_Assign_Rule a
        WHERE
            a.staff_id in (SELECT staff_id FROM @tmpStaff)
            and based_on = 4 
            and a.act_id =@RIGHTCode
END 

IF EXISTS(SELECT 1 FROM @tmpClass WHERE obj_id = 0) 
BEGIN
    IF @IncludeFunctionNode = 1
        SELECT * FROM uds_class WHERE classid = classparentid ORDER BY classtype 
    ELSE
        SELECT  * FROM uds_class WHERE classid = classparentid AND classtype <= 1 ORDER BY classtype 

    -- PRINT '全局'
    RETURN
END
    
DECLARE @id INT
DECLARE @pid INT
DECLARE @curid INT

SET @ids=''

--得到可显示类ID的字符串
SELECT @ids = @ids +  CONVERT(nvarchar,obj_id) +',' FROM @tmpClass;

--删除临时表@tmpClass中所有非根节点的节点
WHILE LEN(@ids)>0
BEGIN
    CREATE TABLE #path (pid int)    
    SET @id = CONVERT(int,substring(@ids,1,charindex(',',@ids)-1))

    --备份原ID
    SET @curid = @id

    --得到此ID的所有父ID到临时Path表
    SELECT @pid=classparentid FROM uds_class WHERE classid=@id    
    WHILE @pid<>@id
    BEGIN
--        PRINT 'pid=' + CONVERT(nvarchar, @pid ) + ':id=' + CONVERT(nvarchar,@id)
        INSERT INTO #path (pid) values(@pid)
        SET @id = @pid
        SELECT @pid=classparentid FROM uds_class WHERE classid=@id
    END
    
    --如果有记录在其所有父ID中,则其为某一节点的子ID,删除之
    IF EXISTS(SELECT 1 FROM @tmpClass WHERE obj_id in (SELECT pid FROM #path))                
        DELETE FROM @tmpClass WHERE obj_id = @curid
        
    DROP TABLE #path
    --剪切字符串
    SET @ids = substring(@ids,charindex(',',@ids)+1,LEN(@ids))    
END

--是否包含功能接点
IF @IncludeFunctionNode = 1 
    --得到拥有的类信息
    SELECT * FROM uds_class WHERE classid IN (SELECT obj_id FROM  @tmpClass) ORDER BY classtype
ELSE
    --得到拥有的类信息,只包含项目和文档
    SELECT * FROM uds_class WHERE classid in (SELECT obj_id FROM  @tmpClass) AND classtype <= 1 ORDER BY classtype
    
END
GO